/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package jsalon.repo;
import jsalon.repo.IOfficerRepo;
import jamjun.infra.common.DBHelper;
import jamjun.infra.common.DataType;
import jamjun.infra.common.SqlParameter;
import jamjun.infra.lang.JJString;
import java.io.File;
import java.net.URL;
import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import jsalon.model.Officer;
import jsalon.model.OfficerFilter;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;


/**
 *
 * @author Java
 */
public class OfficerRepo implements IOfficerRepo  {

    @Override
    public Officer insert(Officer data) throws Exception {
        Connection conn = null;    
        //Statement stmt = null;
        PreparedStatement pstmt = null;
        try { 
            List<SqlParameter> SqlParameterList;
            SqlParameterList = new ArrayList<>();
            conn = DBHelper.getConnection(jamjun.infra.model.DBConnectionName.jsalon.Name());
            String sql = "insert into officer "
                    +"(code, userName, password, firstName, lastName, nickName,birthDay, idCard )"
                    +" values " 
                    +"(@code,@userName,@password,@firstName,@lastName,@nickName,@birthDay,@idCard);";
            SqlParameterList.add(new SqlParameter("@code",data.code, DataType.typeString));
            SqlParameterList.add(new SqlParameter("@userName",data.userName, DataType.typeString));
            SqlParameterList.add(new SqlParameter("@password",data.password, DataType.typeString));
            SqlParameterList.add(new SqlParameter("@firstName",data.firstName, DataType.typeString));
            SqlParameterList.add(new SqlParameter("@lastName",data.lastName, DataType.typeString));
            SqlParameterList.add(new SqlParameter("@nickName",data.nickName, DataType.typeString));
            SqlParameterList.add(new SqlParameter("@idCard",data.idCard, DataType.typeString));
            SqlParameterList.add(new SqlParameter("@birthDay",data.birthDay, DataType.typeDateTime));
            
            pstmt = DBHelper.setPreparedStatement(conn,sql,SqlParameterList);
            pstmt.executeUpdate(); // execute insert statement
            conn.close();
            return data;
        } catch (SQLException ex) {
            Logger.getLogger(OfficerRepo.class.getName()).log(Level.SEVERE, null, ex);
            throw new Exception(ex.getMessage());
        } finally {
            if(pstmt != null) {pstmt.close();}
            if(pstmt != null) {conn.close();} 
        } 
    }

    @Override
    public List<Officer> getList(jamjun.infra.model.Paging paging,OfficerFilter modelFilter) throws Exception{
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try { 
            conn = DBHelper.getConnection(jamjun.infra.model.DBConnectionName.jsalon.Name());
            //<editor-fold desc="model filter.">
            int filterIndex =1;
            String sqlFilterCMD = "";
            String opSQL = " and ";
            List<SqlParameter> SqlParameterList = new ArrayList<>();
            if(!JJString.IsNullOrEmty(modelFilter.code)){
                sqlFilterCMD += (!JJString.IsNullOrEmty(sqlFilterCMD)) ? opSQL : "";
                sqlFilterCMD += " code=@code ";
                SqlParameter sqlParameter = new  SqlParameter();
                sqlParameter.SetString("@code",modelFilter.code);
                SqlParameterList.add(sqlParameter);
            }
            if(!JJString.IsNullOrEmty(modelFilter.firstName)){
                sqlFilterCMD += (!JJString.IsNullOrEmty(sqlFilterCMD)) ? opSQL : "";
                sqlFilterCMD += " firstName=@firstName ";
                SqlParameter sqlParameter = new  SqlParameter();
                sqlParameter.SetString("@firstName",modelFilter.firstName);
                SqlParameterList.add(sqlParameter);
            }
            if(modelFilter.birthDay != null){
                sqlFilterCMD += (!JJString.IsNullOrEmty(sqlFilterCMD)) ? opSQL : "";
                sqlFilterCMD += " Date(birthDay)=Date(@birthDay) ";
                SqlParameter sqlParameter = new  SqlParameter();
                sqlParameter.setDate("@birthDay",modelFilter.birthDay);
                SqlParameterList.add(sqlParameter);
            }
            sqlFilterCMD = (!JJString.IsNullOrEmty(sqlFilterCMD)) ? " where " + sqlFilterCMD : "";
            //</editor-fold>
            int skip = paging.PageNo * paging.PageSize;
            String sql = "SELECT * FROM officer " + sqlFilterCMD;
            sql = "select * from ("+sql+") as t1 LIMIT "+paging.getPageIndex()+","+ skip;
            pstmt = DBHelper.setPreparedStatement(conn,sql,SqlParameterList);
            rs = pstmt.executeQuery();
            List<Officer> modelList = new ArrayList<>();
            while(rs.next()){
               Officer model = new Officer();
               model.domainID = rs.getString("domainID");
               model.code = rs.getString("code");
               model.userName = rs.getString("userName");
               model.password = rs.getString("password");
               model.firstName  = rs.getString("firstName");
               model.lastName  = rs.getString("lastName");
               model.nickName = rs.getString("nickName");
               model.idCard = rs.getString("idCard");
               model.birthDay = rs.getDate("birthDay");
               modelList.add(model);
            }
            rs.close();
            return modelList;
        } catch (SQLException ex) {
            Logger.getLogger(OfficerRepo.class.getName()).log(Level.SEVERE, null, ex);
            throw ex;
        } catch (Exception ex){
            throw ex;
        } finally {
            if(rs != null){rs.close(); }
            if(conn != null){conn.close();}
        } 
        
        
    }
    
}
